package com.hdl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.hdl.dto.ArticleSearch;
import com.hdl.entity.Article;
import com.hdl.util.JdbcUtil;
import com.hdl.vo.ArticleVO;

public class ArticleDAO {

	/**
	 * 根据传入的search查询条件search.getKey()查询总数
	 * 
	 * @param search 查询条件
	 * @return 总数
	 * @throws SQLException
	 */
	public long countBySearch(ArticleSearch search) throws SQLException {
		String sql = "SELECT count(*) as c FROM article INNER JOIN article_type ON article.ARTICLE_TYPE_ID = article_type.ARTICLE_TYPE_ID ";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtil.getConnection();
			if (search.getKey() != null && !search.equals("")) {
				sql += " AND article.ARTICLE_TITLE like concat('%',?,'%')";
				pstmt = conn.prepareStatement(sql);
				pstmt.setObject(1, search.getKey());
			} else {
				pstmt = conn.prepareStatement(sql);
			}
			rs = pstmt.executeQuery();
			rs.next();
			return rs.getLong(1);
		} finally {
			JdbcUtil.close(rs, pstmt, conn);
		}
	}

	/**
	 * 根据传入search查询条件中的search.getpageSize()每页最大数和search.getpageNumber()页码数还有search.getKey()模糊查询来分页查询
	 * 
	 * @param search
	 * @return 新闻文章列表
	 * @throws SQLException
	 */
	public List<ArticleVO> selectSearch(ArticleSearch search, int index, int len) throws SQLException {
		String sql = "SELECT article.*,article_type.ARTICLE_TYPE_NAME FROM article INNER JOIN article_type ON article.ARTICLE_TYPE_ID = article_type.ARTICLE_TYPE_ID ";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtil.getConnection();
			if (search.getKey() != null && !search.equals("")) {
				sql += " where article.ARTICLE_TITLE like concat('%',?,'%') order by article.ARTICLE_TYPE_ID limit ?,?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setObject(1, search.getKey());
				pstmt.setObject(2, index);
				pstmt.setObject(3, len);
			} else {
				sql += " order by article.ARTICLE_TYPE_ID limit ?,?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setObject(1, index);
				pstmt.setObject(2, len);
			}
			rs = pstmt.executeQuery();
			List<ArticleVO> list = new ArrayList<ArticleVO>();
			while (rs.next()) {
				ArticleVO entity = new ArticleVO();
				entity.setArticleId(rs.getLong("ARTICLE_ID"));
				entity.setArticleCode(rs.getString("ARTICLE_CODE"));
				entity.setArticleImgs(rs.getString("ARTICLE_IMGS"));
				entity.setArticleTime(rs.getTimestamp("ARTICLE_TIME"));
				entity.setArticleTitle(rs.getString("ARTICLE_TITLE"));
				entity.setArticleWorker(rs.getNString("ARTICLE_WORKER"));
				entity.setArticleTypeName(rs.getString("ARTICLE_TYPE_NAME"));
				entity.setArticleContent(rs.getString("ARTICLE_CONTENT"));
				list.add(entity);
			}
			return list;
		} finally {
			JdbcUtil.close(rs, pstmt, conn);
		}
	}
	
	/**
	 * - 传入一个Article来添加文章
	 * 
	 * @param article
	 * @throws SQLException
	 */
	public int add(Article article) throws SQLException {
		String sql = "INSERT INTO article (ARTICLE_ID,ARTICLE_TYPE_ID,ARTICLE_CODE,ARTICLE_TITLE,ARTICLE_WORKER,ARTICLE_CONTENT,ARTICLE_IMGS,ARTICLE_TIME,ARTICLE_STATUS) VALUES (?,?,?,?,?,?,?,?,?)";
		try (Connection conn = JdbcUtil.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql);) {
			JdbcUtil.setParams(pstmt, JdbcUtil.nextId(), -1, article.getArticleCode(),
					article.getArticleTitle(), "嗨底捞", article.getArticleContent(),
					article.getArticleImgs(), new Date(),1);

			return pstmt.executeUpdate();
		}
	}
	
	/**
	 * - 传入一个Article，根据主键来修改文章
	 * 
	 * @param article
	 * @throws SQLException
	 */
	public int updata(Article article) throws SQLException {
		String sql = "update article set ARTICLE_TYPE_ID=?,ARTICLE_CODE=?,ARTICLE_TITLE=?,ARTICLE_WORKER=?,ARTICLE_CONTENT=?,ARTICLE_IMGS=?,ARTICLE_TIME=? where ARTICLE_ID=?,";
		try (Connection conn = JdbcUtil.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql);) {
			JdbcUtil.setParams(pstmt, article.getArticleTypeId(), article.getArticleCode(),
					article.getArticleTitle(), article.getArticleWorker(), article.getArticleContent(),
					article.getArticleImgs(), new Date(),article.getArticleId());

			return pstmt.executeUpdate();
		}
	}
	
	/**
	 * - 根据主键获取一个文章
	 * @param articleId
	 * @return
	 * @throws SQLException
	 */
	public Article selectById(Long articleId) throws SQLException{
		String sql = "select * from article where ARTICLE_ID=?";// 禁止使用*
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, articleId);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				Article entity = new Article();
				entity.setArticleId(rs.getLong("ARTICLE_ID"));
				entity.setArticleCode(rs.getString("ARTICLE_CODE"));
				entity.setArticleImgs(rs.getString("ARTICLE_IMGS"));
				entity.setArticleTime(rs.getTimestamp("ARTICLE_TIME"));
				entity.setArticleTitle(rs.getString("ARTICLE_TITLE"));
				entity.setArticleWorker(rs.getNString("ARTICLE_WORKER"));
				entity.setArticleContent(rs.getString("ARTICLE_CONTENT"));
				return entity;
			} else {
				throw new SQLException("没有对应的记录");
			}
		} finally {
			JdbcUtil.close(rs, pstmt, conn);
		}
	}

	/**
	 * 根据主键删除一个文章
	 * @param articleId - 主键
	 * @return
	 * @throws SQLException
	 */
	public int deleteById(Long articleId) throws SQLException  {
		String sql = "delete from article where ARTICLE_ID=?";
		try (Connection conn = JdbcUtil.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql);) {
			pstmt.setObject(1, articleId);
			return pstmt.executeUpdate();
		}
	}
	
	/**
	 * - 上一篇
	 * @param articleId
	 * @return
	 * @throws SQLException
	 */
	public Article selectPrevious(Long articleId)throws SQLException {
//		String sql = "SELECT * from article WHERE ARTICLE_TYPE_ID= (SELECT ARTICLE_TYPE_ID FROM article where article_id=?) AND ARTICLE_TIME < (SELECT ARTICLE_TIME FROM article where article_id=?)  ORDER BY  ARTICLE_TIME desc LIMIT 1);";
		String sql = "SELECT * from article WHERE ARTICLE_TYPE_ID= (SELECT ARTICLE_TYPE_ID FROM article where article_id=?) AND ARTICLE_TIME < (SELECT ARTICLE_TIME FROM article where article_id=?)  ORDER BY  ARTICLE_TIME desc LIMIT 1";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, articleId);
			pstmt.setObject(2, articleId);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				Article entity = new Article();
				entity.setArticleId(rs.getLong("ARTICLE_ID"));
				entity.setArticleCode(rs.getString("ARTICLE_CODE"));
				entity.setArticleImgs(rs.getString("ARTICLE_IMGS"));
				entity.setArticleTime(rs.getTimestamp("ARTICLE_TIME"));
				entity.setArticleTitle(rs.getString("ARTICLE_TITLE"));
				entity.setArticleWorker(rs.getNString("ARTICLE_WORKER"));
				entity.setArticleContent(rs.getString("ARTICLE_CONTENT"));
				return entity;
			} else {
				throw new SQLException("没有对应的记录");
			}
		} finally {
			JdbcUtil.close(rs, pstmt, conn);
		}
	}
	
	/**
	 * - 下一篇
	 * @param articleId
	 * @return
	 * @throws SQLException
	 */
	public Article selectNext(Long articleId)throws SQLException {
		String sql = "SELECT * from article WHERE ARTICLE_TYPE_ID= (SELECT ARTICLE_TYPE_ID FROM article where article_id=?) AND ARTICLE_TIME > (SELECT ARTICLE_TIME FROM article where article_id=?)  ORDER BY  ARTICLE_TIME LIMIT 1";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, articleId);
			pstmt.setObject(2, articleId);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				Article entity = new Article();
				entity.setArticleId(rs.getLong("ARTICLE_ID"));
				entity.setArticleCode(rs.getString("ARTICLE_CODE"));
				entity.setArticleImgs(rs.getString("ARTICLE_IMGS"));
				entity.setArticleTime(rs.getTimestamp("ARTICLE_TIME"));
				entity.setArticleTitle(rs.getString("ARTICLE_TITLE"));
				entity.setArticleWorker(rs.getNString("ARTICLE_WORKER"));
				entity.setArticleContent(rs.getString("ARTICLE_CONTENT"));
				return entity;
			} else {
				throw new SQLException("没有对应的记录");
			}
		} finally {
			JdbcUtil.close(rs, pstmt, conn);
		}
	}
}
